ONESOURCE

Workpapers Help

Functions: Get

Screen capture showing the Function button options.

Functions helps you build workpaper formulas, and you can use it for building Get formulas to pull data from a ONESOURCE product. Workpapers uses two types of Get formulas depending on the ONESOURCE product you use. The Function Wizard detects your product and automatically selects the appropriate type of formula:

  • TRGetField: Pulls a single field from a ONESOURCE product into a workpaper.
  • TRGetRange: Pulls a range of fields from a ONESOURCE product into a workpaper.

Follow these guidelines for formulas:

  • TRGetField Formulas: The Add-in cannot retrieve more than 3,400 TRGetField formulas at a time. If a workbook has more than 3,400 formulas, use the Retrieve Selected Data function.
  • TRGetRange Formulas: A GetRange formula cannot retrieve more than 2,000,000 cells of data within the Add-in. If a formula retrieves more than 2,000,000 cells, use the Batches feature.
  • Length: Formulas cannot exceed 8,192 characters.
  • Volatile Functions: Formulas should not contain volatile functions, such as RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), and INFO(). Volatile functions slow down Add-in calculations because each instance of these functions is executed when Excel recalculates.
  • Nested Functions: Formulas should not contain nested functions since there are some limitations in calculations, including precedence chain calculations.

To create a Get formula:

  1. On the Workpapers tab, select the Functions icon.

    Screen capture showing the Functions button in the main ribbon.

  2. In the Function Wizard, select Get data in the first drop-down list, and then for the remaining drop-down lists, select components to identify your data.

    The number of drop-downs displayed depends on the ONESOURCE product you are using, and if you hover over the information icon in the lower-right, it displays the remaining fields you need to complete.

    When you select values in the drop-down lists, reference codes—the actual values sent to ONESOURCE—are inserted directly into the formula. If you prefer to store and look up reference codes in workpaper cells, see Functions: References and Anchoring.

    Screen capture showing the options for the first screen of the Function Wizard.

  3. Select Next.
  4. If you see fields under the heading Select Parameters, select values in each parameter drop-down list.

    Screen capture showing how to select formula parameters.

    • Red asterisks indicate required values.

    • Some ONESOURCE products offer the following in parameter drop-downs:

      • All (Perpetual): This acts like a wild card that selects all parameter values and includes subsequent new values from the Platform.
      • All (Point in time): This selects all the current parameter values and does not include subsequent new values from the Platform.
  5. Select Next.
  6. Enter the Target Range, which is the cell or cells that receive the data from the Platform.

    If you want to anchor Target Range, select the appropriate check box. For TRGetRange formulas, you can anchor the column or row—not both:

    • Select the check box with the down arrow to anchor the column.
    • Select the check box with the right arrow to anchor the row.

    Screen capture showing how to set the target range.

    The information icon in the lower-right changes to a check mark when the formula is complete: Screen capture of the check mark icon.

  7. If your formula is TRGetRange, you have the additional option Transpose to switch the layout of the results from rows to columns (or columns to rows).

    Screen capture showing the Transpose option for TRGetRange formulas.

  8. Select Done.

    The formula cell is updated with the current date and time. The target cell is highlighted in blue for TRGetField formulas only.